package DBLayer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


import DomainLayer.Journal;
import DomainLayer.LabSchemeMeasurement;
import DomainLayer.NursingDocumentation;


public class DBNursingDocumentation {
	
	private Connection con;
	public DBNursingDocumentation(){
		con = DbConnection1.getInstance().getDBcon();
		}
	
	public int createNursingDocumentation(NursingDocumentation doc) throws Exception {
		int rc = -1;
		String query = "INSERT INTO MeasurementsNursingDoc (nursDocName, date, communications, development, respiration_circulation, nutrition, excretion, skin_tissue, activity, sleep, pain_sensation, sexuality_reproduction, psychsocial, spiritual_curtural) VALUES('" + 
				doc.getName() + "', '" +
				doc.getDate() + "', '" +
				doc.getCommunication() + "', '" +
				doc.getDevelopment() + "', '" +
				doc.getRespiration_circulation() + "', '" +
				doc.getNutrition() +  "', '" +
				doc.getExcretion() + "', '" +
				doc.getSkin_tissue() + "', '" +
				doc.getActivity() + "', '" +
				doc.getSleep() + "', '" +
				doc.getPain_sensation() + "', '" +
				doc.getSexuality_reproduction() + "', '" +
				doc.getPsychsocial() + "', '" +
				doc.getSpiritual_cultural() + "'"+
				")" ;
		System.out.println("insert : " + query);
		
		try{Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				rc = stmt.executeUpdate(query);
				stmt.close();
				}catch(SQLException ex){
					System.out.println("customer does not exist");
					throw new Exception ("insertion was not correct");
				}
		return rc;
}
	
	
	public int deleteNursingDocumentation(String name) {
		int rc = -1;
		String query="DELETE FROM MeasurementsNursingDoc WHERE name = '" +
				name + "'";
	         System.out.println(query);
		try{ // delete from employee
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
		  	rc = stmt.executeUpdate(query);
		  	stmt.close();
		}//slut try	
	        catch(Exception ex){
		  	System.out.println("Delete exception in customer db: "+ex);
	        }
		return rc;
	}
	
	public int updateScheme(NursingDocumentation nurseDoc) {
		
		NursingDocumentation docObj  = nurseDoc;
		int rc=-1;
	  
		String query="UPDATE MeasurementsNursingDoc SET "+
		 	  "date ='"+ docObj.getDate() + "', " +
		 	  "communications ='"+ docObj.getCommunication() + "', "+
		 	  "development ='" + docObj.getDevelopment() + "', "+
		 	  "respiration_circulation ='" + docObj.getRespiration_circulation() + "', "+
		 	  "nutrition ='" + docObj.getNutrition() + "', "+
		 	  "excretion ='" + docObj.getExcretion() + "', "+
		 	  "skin_tissue ='" + docObj.getSkin_tissue() + "', "+
		 	  "activity ='" + docObj.getActivity() + "', "+
		 	  "sleep ='" + docObj.getSleep() + "', "+
		 	  "pain_sensation ='" + docObj.getPain_sensation() + "', "+
		 	  "sexuality_reproduction ='" + docObj.getSexuality_reproduction() + "', "+
		 	  "psychosial ='" + docObj.getPsychsocial() + "', "+
		 	  "spiritual_curtural ='" + docObj.getSpiritual_cultural() + "'" +
		          " WHERE name = '"+ docObj.getName() + "'";
                System.out.println("Update query:" + query);
  		try{ // update employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);
                      
	 	 	stmt.close();
		}//end try	
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in employee db: "+ex);
	  	}
		return(rc);
		
	}
	
	public NursingDocumentation findNursingDocumentation(String name) {
		String wClause = " name = '" + name + "'";
		return singleWhere(wClause);
	}
	
	 private NursingDocumentation singleWhere(String wClause)
	  {
	   ResultSet results;
	   NursingDocumentation docObj = new NursingDocumentation("");

	   	String query =  buildQuery(wClause);
	                 System.out.println(query);
	   try{ // read the employee from the database
	     Statement stmt = con.createStatement();
	     stmt.setQueryTimeout(5);
	     results = stmt.executeQuery(query);
	     
	     if(results.next() ){
	                             docObj = buildNursingDocumentation(results);
	                             //association is to be build
	                            
	                             stmt.close();

	     			}
	                         else{ //no customer was found
	                             docObj = null;
	                         }
	   }//end try 
	    catch(Exception e){
	     System.out.println("Query exception: "+e);
	    }
	   return docObj;
	  }
	 
	 private String buildQuery(String wClause)
	 {
	  String query="SELECT nursDocName, date, communications, development, respiration_circulation, nutrition, excretion, skin_tissue, activity, sleep, pain_sensation, sexuality_reproduction, psychsocial, spiritual_curtural FROM MeasurementsNursingDoc";
	  
	  if (wClause.length()>0)
	   query=query+" WHERE "+ wClause;
	   
	  return query;
	 }
	 
	 private NursingDocumentation buildNursingDocumentation(ResultSet results)
     {   NursingDocumentation docObj = new NursingDocumentation("");
         try{ // the columns from the table emplayee  are used
        	 docObj.setName(results.getString(1));
        	 docObj.setDate(results.getString(2));
        	 docObj.setCommunication(results.getString(3));
        	 docObj.setDevelopment(results.getString(4));
        	 docObj.setRespiration_circulation(results.getString(5));
        	 docObj.setNutrition(results.getString(6));
        	 docObj.setExcretion(results.getString(7));
        	 docObj.setSkin_tissue(results.getString(8));
        	 docObj.setActivity(results.getString(9));
        	 docObj.setSleep(results.getString(10));
        	 docObj.setPain_sensation(results.getString(11));
        	 docObj.setSexuality_reproduction(results.getString(12));
        	 docObj.setPsychsocial(results.getString(13));
        	 docObj.setSpiritual_cultural(results.getString(14));
        	               
                 }
        catch(Exception e)
        {
            System.out.println("error in building the customer object");
        }
        return docObj;
     }

	 /**
	  * Get all NursingDocumentation parts 
	  * @param name - the name of the nursing documentation
	  * @return
	  */
	public ArrayList<NursingDocumentation> getAllNursingDocumentationParts(String name) {
		
		
		return miscWhere(" nursDocName='"+name+"'");
		
	}
	
	private ArrayList<NursingDocumentation> miscWhere(String wClause) {
		ResultSet results;
		ArrayList<NursingDocumentation> list = new ArrayList<NursingDocumentation>();

		String query = buildQuery(wClause);
        System.out.println(query);
		try { // read the employee from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				NursingDocumentation scheme = new NursingDocumentation();
				scheme = buildNursingDocumentation(results);
				list.add(scheme);
			}// end while
			stmt.close();

		}//
		catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;

	}
	
	
	public ArrayList<NursingDocumentation> getNursingDocumentationForJournal(Journal journal){
		 ResultSet results;
		 ArrayList<NursingDocumentation> list = new ArrayList<NursingDocumentation>();
		
		 String query="SELECT * FROM MeasurementsNursingDoc where nursDocName=(select top 1 nursingDocName From NursingDocJournal WHERE JournalName='" +journal.getName()+"'"
				 + "and contactCauses='" +journal.getContact_causes()+ "')";
		 System.out.println("Query getNursingDocumentationForJournal:" + query);
		 
		 try { // read the employee from the database
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				results = stmt.executeQuery(query);

				while (results.next()) {
					NursingDocumentation nursingDoc = new NursingDocumentation();
					nursingDoc = buildNursingDocumentation(results);
					list.add(nursingDoc);
				}// end while
				stmt.close();

			}// slut try
			catch (Exception e) {
				System.out.println("Query exception - select: " + e);
				e.printStackTrace();
			}
			return list;
		
		 
		  
		  
		   
		   
		  
		 
		 
	 }
	
}
